import csv
from ReportClass import ReportC
from models import ReportSum, Report, Person, ReportGraph
from string import lstrip, lower
from django.db.models.sql import where
from django.db.models import Avg, Max, Min, Count
from django.db import connection, transaction


def handle_uploaded_file(f):
    testReader=csv.reader(f,delimiter=',', quotechar='|')
    #now the testReader is an array,so we can iterate it
    
    ##################################################
    #read each line in file, add data to Report class
    ##################################################
    flag = 0
    for row in testReader:
        #print "#".join(row[0])
        if row[0] == 'Destination':
            flag = 1
            i = 0
        
        #add data from file to class Report
        
        if(flag == 2):
            type = lower(row[0])
            if(type.find('mobile') != -1):
                row[0] = type.replace('mobile', 'LandLine')
            if(row[0] == '-'):
                row[0] = 'Unknown country'
                        
                            
                
            ReportC.Destination.append(row[0]) 
            ReportC.DestinationCode.append(row[1])
            ReportC.VendorRefNo.append((row[2]))
            ReportC.VendorName.append((row[3]))
            ReportC.Date.append((row[4]))
            ReportC.Day.append((row[5]))
            ReportC.Attempts.append((row[6]))
            ReportC.Completed.append((row[7]))
            ReportC.RawDur.append((row[10]))
            ReportC.RndDur.append((row[11]))
            ReportC.Cost.append((row[15]))
            ReportC.CostPerMin.append((row[16]))
            
            #insert Report to DB
            p = Report(Destination= ReportC.Destination[i],
                       DestinationCode = ReportC.DestinationCode[i],
                       VendorRefNo = ReportC.VendorRefNo[i],
                       VendorName = ReportC.VendorName[i],
                       Date = ReportC.Date[i],
                       Day = ReportC.Day[i],
                       Attempts = int(row[6]),
                       Completed = int(row[7]),
                       RawDur = ReportC.RawDur[i],
                       RndDur = ReportC.RndDur[i],
                       Cost = ReportC.Cost[i],
                       CostPerMin = ReportC.CostPerMin[i])
            #p2 = t_location_prefix(PREFIX
                                   #LOCATION
                                   #COUNTRY
                                   #TYPE)
            
            p.save()
            
            
            ##############
            #get country
            country = row[0]
            if(country.find('-') != -1):
                index = country.find('-')   #save index of '-' in country string
                country = country[:index]
                if(is_number(country) == True): #if first element in country is number
                    #find country name in the next element
                    country =  lstrip(row[0][index+1:])
                    if(country.find('-') != -1): # if the thrid element have '-'
                        index = country.find('-')
                        country = country[:index]
                      
            #get type
            type = lower(row[0])
            if(type.find('landline') != -1):    #mobile
                type = '1'
            else:   
                type = '0'
            
            #count min, avg, max Code
            avg = Report.objects.extra(where=['Destination=%s'], params=[row[0]])
            avg = avg.aggregate(Avg('Cost'))
            
            min = Report.objects.extra(where=['Destination=%s'], params=[row[0]])
            min = min.aggregate(Min('Cost'))
            
            max = Report.objects.extra(where=['Destination=%s'], params=[row[0]])
            max = max.aggregate(Max('Cost'))
            
            #print float(min['Cost__min'])
        #insert Report to DB
            if(row[6]=='0'):
                a = 0
            else:
                a = round(float((float(row[7])/float(row[6]))*100), 2)
            if(row[7]=='0'):
                b = 0
            else:
                b = round((float(row[10])/float(row[7]))*100, 4)
            
            p2 = ReportSum(Date= row[4],
                           Country = country,
                           Type = type,
                           VendorName = row[3],
                           Attempts = row[6],
                           Completed = row[7],
                           ASR = str(a),
                           RawDur = str(row[10]),
                           ACD = str(b),
                           MinCode = str(min['Cost__min']),
                           AvgCode = str(avg['Cost__avg']),
                           MaxCode = str(max['Cost__max']))
                
            p2.save()
            ##############
            i = i + 1
        if(flag == 1):
            flag = 2
    insertToReportGraph()
    
    #insertToReportSum() #call this function to import Data to ReportSum in DB
    #test
from django.db.models import Count, Sum
def insertToReportGraph():
    reports = ReportSum.objects.values('Country').annotate(Attempts=Sum('Attempts'), Completed=Sum('Completed'), ASR=Sum('ASR'), ACD=Sum('ACD'), RawDur=Sum('RawDur'))
    #print reports
    for report in reports:
        p = ReportGraph(Country = report['Country'],
                    Attempts = report['Attempts'],
                    Completed = report['Completed'],
                    ASR = report['ASR'],
                    ACD = report['ACD'],
                    RawDur = report['RawDur'])
        p.save()
    
    
    
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        return False


def insertToReportSum():
    reportDS = Report.objects.all()
    for row in reportDS:
        #get country
        country = row.Destination
        if(country.find('-') != -1):
            index = country.find('-')   #save index of '-' in country string
            country = country[:index]
            if(is_number(country) == True): #if first element in country is number
                #find country name in the next element
                country =  lstrip(row.Destination[index+1:])
                if(country.find('-') != -1): # if the thrid element have '-'
                    index = country.find('-')
                    country = country[:index]
                    
        #get type
        type = lower(row.Destination)
        if(type.find('landline') != -1):    #mobile
            type = '1'
        else:   
            type = '0'

        #count min, avg, max Code
        #avg = Report.objects.extra(where=['Destination=%s'], params=['Unknown country'])
        #avg = avg.aggregate(Avg('Cost'))
    for rows in ReportC:
        print rows.Destination
'''        print 'bbbbbbbbbbbbbbbbbbbbbbbbbbb'
        min = Report.objects.extra(where=['Destination=%s'], params=[row.Destination])
        min = min.aggregate(Min('Cost'))
        print 'ccccccccccccccccccccccccc'
        max = Report.objects.extra(where=['Destination=%s'], params=[row.Destination])
        max = max.aggregate(Max('Cost'))
        print 'dddddddddddddddddddddddd'
        #print float(min['Cost__min'])
    #insert Report to DB
        a = round(float((float(26)/float(83))*100), 2)
        b = round((float(86.22)/float(26))*100, 4)
        print a
        p = ReportSum(Date= row.Date,
                       Country = country,
                       Type = type,
                       VendorName = row.VendorName,
                       Attempts = row.Attempts,
                       Completed = row.Completed,
                       ASR = str(a),
                       RawDur = str(row.RawDur),
                       ACD = str(b),
                       MinCode = str(min['Cost__min']),
                       AvgCode = str(avg['Cost__avg']),
                       MaxCode = str(max['Cost__max']))
            
        p.save()
    
    #print a
    #print b
    #p = ReportSum(Date= '3/3/2000',
     #                  Country = 'asd',
      #                 Type = 0,
       #                VendorName = 'ven',
        #               Attempts = 83,
         #              Completed = 26,
          #             ASR = '10.02',
           #            RawDur = '10.02',
            #           ACD = '10.0211',
             #          MinCode = '10.0211',
              #         AvgCode = '10.0211',
               #        MaxCode = '10.0211')
    #print p.ASR
    #p.save()

    
    
    '''
    
    
    
    